﻿using System;
using System.Collections.Specialized;
using System.Data;
using System.Text;

namespace AutoUnitTestEngine.Helpers
{
    public class SqlBuilder
    {
        public static string CreateInsertSql(DataRow row, string dbTableName,StringCollection scNotExitingDbColNames )
        {
            var scInsertColNames = new StringCollection();
            foreach(DataColumn col in row.Table.Columns)
            {
                if (scNotExitingDbColNames != null && scNotExitingDbColNames.Contains(col.ColumnName))
                    continue;
                scInsertColNames.Add(col.ColumnName);
            }
            return CreateInsertSqlByInsertColNames(row, dbTableName, scInsertColNames);
        }

        public static string GetColValueByDataType(DataRow row, string colName)
        {
            var reVal = "";
            var colType = row.Table.Columns[colName].DataType;
            var colVal = Convert.ToString(row[colName]);
            if (colType.Equals(typeof(string)))
            {
                reVal = ("'" + colVal + "'");
            }
            else if (colType.Equals(typeof(Guid))) reVal = ("'"+colVal.Replace("-","")+"'");
            else
                reVal = (colVal);
            return reVal;
        }

        public static string CreateInsertSqlByInsertColNames(DataRow row, string dbTableName, StringCollection scInsertColNames)
        {
            StringBuilder sql = new StringBuilder();
            var columns = new StringBuilder();
            var values = new StringBuilder();
            for (var i = 0; i < scInsertColNames.Count; i++ )
            {
                var colName = scInsertColNames[i];
                var colVal = Convert.ToString(row[colName]);
                
                if (!String.IsNullOrEmpty(colVal))
                {
                    if (columns.Length > 0)
                        columns.Append(",");
                    columns.Append(colName);
                    if (values.Length > 0)
                        values.Append(",");
                    values.Append(GetColValueByDataType(row, colName));
                }
            }
            if (columns.Length > 0)
                sql.Append("Insert into ").Append(dbTableName).Append(" ( ").Append(columns).Append(" ) ").Append(" values ").Append(" ( ").Append(values).Append(" ) ");
            return sql.ToString();
        }

        public static string CreateUpdateSql(DataRow row, string dbTableName,StringCollection updateColNames, StringCollection keyColNames)
        {
            var nvcUpdateColNameAndValue = new NameValueCollection();
            var nvcKeyColNameAndValue = new NameValueCollection();

            foreach (var strColName in updateColNames)
            {
                var colVal = Convert.ToString(row[strColName]);
                nvcUpdateColNameAndValue.Add(strColName, GetColValueByDataType(row,strColName));
            }
            foreach (var strColName in keyColNames)
            {
                var colVal = Convert.ToString(row[strColName]);
                nvcKeyColNameAndValue.Add(strColName, GetColValueByDataType(row, strColName));
            }
            return CreateUpdateSql(dbTableName,nvcUpdateColNameAndValue,nvcKeyColNameAndValue);
        }

        public static string CreateUpdateSql(string dbTableName,NameValueCollection nvcUpdateColNameAndValue,NameValueCollection nvcKeyColNameAndValue)
        {
            var sql = new StringBuilder();
            var keyCondition = new StringBuilder();
            for(var i=0;i<nvcKeyColNameAndValue.Count;i++)
            {
                keyCondition.Append(keyCondition.Length == 0 ? " Where " : " And ");
                keyCondition.Append(nvcKeyColNameAndValue.GetKey(i) + "=" + nvcKeyColNameAndValue.Get(i));
            }
            for(var i=0;i<nvcUpdateColNameAndValue.Count;i++)
            {
                sql.Append(sql.Length == 0 ? "Update " + dbTableName + " Set " : ",");
                sql.Append(nvcUpdateColNameAndValue.GetKey(i) + "="+nvcUpdateColNameAndValue.Get(i));
            }
            sql.Append(keyCondition);
            return sql.ToString();
        }

        public static string GetRtrimColSql(string colName, string shotTableName, string alias)
        {
            return "RTrim(" + (string.IsNullOrEmpty(shotTableName) ? "" : shotTableName+".") + colName + ") AS " + (string.IsNullOrEmpty(shotTableName) ? colName : alias) + " ";
        }

        public static string GetRtrimColSql(string colName, string shotTableName)
        {
            return GetRtrimColSql(colName,shotTableName,colName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="nvcKeyInfos"></param>
        /// <returns></returns>
        public static string CreateCondition(NameValueCollection[] nvcKeyInfos)
        {
            var sbOr = new StringBuilder();
            foreach(var nvc in nvcKeyInfos)
            {
                if (sbOr.Length > 0) sbOr.Append(" OR ");
                if (nvc.Count > 0)
                {
                    sbOr.Append(" ( ");
                    var sbAnd = new StringBuilder();
                    for (var i = 0; i < nvc.Count; i++)
                    {
                        if (sbAnd.Length > 0) sbAnd.Append(" And ");
                        sbAnd.Append(nvc.GetKey(i) + "='" + nvc.Get(i) + "'");
                    }
                    sbOr.Append(sbAnd);
                    sbOr.Append(" ) ");
                }
            }
            return " Where ( "+ sbOr +")";
        }

        public static string CreateSingleTableQueryString(string tableName,DataColumnCollection cols, string condition)
        {
            var querySql = new StringBuilder();
            for(var i=0;i<cols.Count;i++)
            {
                if (querySql.Length > 0) querySql.Append(",");
                querySql.Append(GetRtrimColSql(cols[i].ColumnName,null));
            }
            return "Select "+querySql+" from " + tableName + " " + condition;
        }
    }
}